﻿'*******************************************************************
' Program: Data Center Visio Rack Tool
' Author: Albert E Edlund
' Date:
'
' Purpose:
'
'*******************************************************************

Imports Microsoft.Practices.EnterpriseLibrary.Common
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.ExceptionHandling



Imports System
Imports System.ComponentModel
Imports System.Collections.Generic


Imports System.Collections.ObjectModel


Imports System.Data
Imports System.IO
Imports System.Xml
Imports System.Collections
Imports System.Runtime.InteropServices

Imports visRackAppConnectivity.clsDC_RackData


Public Class clsAppSolutions


#Region " Properties and declarations "

    Public m_db As Database
    Public m_ds As DataSet
    Public m_dt As DataTable

    Public m_dc As dc_appsol
    Public m_oc As Collection(Of dc_appsol)

    Private strUserId As String

    Public m_deleteCommand As DbCommand
    Public m_insertCommand As DbCommand
    Public m_updateCommand As DbCommand

    Public Const readAll As String = "selecttblAppSolution"

    ' who am I
    Private myEnum As DC_RackTables = DC_RackTables.tblAppSolution

#End Region

#Region " Move/Transform Data "


    '
    ' create columns
    '
    Public Sub CreateTableColumns(ByRef tempTable As DataTable)

        Dim newDC As dc_appsol = New dc_appsol(strUserId)

        Try

            Dim colappsolKey As New System.Data.DataColumn
            colappsolKey.ColumnName = "appsolKey"
            colappsolKey.DataType = GetType(Int32)
            colappsolKey.DefaultValue = newDC.appsolKey
            tempTable.Columns.Add(colappsolKey)

            Dim colappsolClientKey As New System.Data.DataColumn
            colappsolClientKey.ColumnName = "appsolClientKey"
            colappsolClientKey.DataType = GetType(Int32)
            colappsolClientKey.DefaultValue = newDC.appsolClientKey
            tempTable.Columns.Add(colappsolClientKey)

            '********************************************
            ' add columns
            '********************************************

            Dim colappsolName As New System.Data.DataColumn
            colappsolName.ColumnName = "appsolName"
            colappsolName.DataType = GetType(String)
            colappsolName.DefaultValue = newDC.appsolName
            tempTable.Columns.Add(colappsolName)

            Dim colappsolComments As New System.Data.DataColumn
            colappsolComments.ColumnName = "appsolComments"
            colappsolComments.DataType = GetType(String)
            colappsolComments.DefaultValue = newDC.appsolComments
            tempTable.Columns.Add(colappsolComments)

            Dim colappsolLastUpdate As New System.Data.DataColumn
            colappsolLastUpdate.ColumnName = "appsolLastUpdate"
            colappsolLastUpdate.DataType = GetType(Date)
            colappsolLastUpdate.DefaultValue = newDC.appsolLastUpdate
            tempTable.Columns.Add(colappsolLastUpdate)

            Dim colappsolLastEdit As New System.Data.DataColumn
            colappsolLastEdit.ColumnName = "appsolLastEdit"
            colappsolLastEdit.DataType = GetType(String)
            colappsolLastEdit.DefaultValue = newDC.appsolLastEdit
            tempTable.Columns.Add(colappsolLastEdit)

            newDC = Nothing

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        If newDC IsNot Nothing Then
            newDC = Nothing
        End If

    End Sub


    '
    ' use for writing records
    '
    Public Sub movePropertiesToDataRow _
            (ByRef tempRow As DataRow, _
             ByVal tempDC As dc_AppSol)

        Try

            With tempDC
                tempRow.SetField("appsolKey", .appsolKey)
                tempRow.SetField("appsolClientKey", .appsolClientKey)
                '*******************************************************************************
                tempRow.SetField("appsolName", .appsolName)

                tempRow.SetField("appsolComments", .appsolComments)
                tempRow.SetField("appsolLastUpdate", .appsolLastUpdate)
                tempRow.SetField("appsolLastEdit", strUserId)
            End With



        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow)

        Try

            With m_dc
                .initProperties()
                .appsolKey = tempRow.Item("appsolKey")
                .appsolClientKey = tempRow.Item("appsolClientKey")
                '************************************************
                .appsolName = tempRow.Item("appsolName")

                .appsolComments = tempRow.Item("appsolComments")
                .appsolLastUpdate = tempRow.Item("appsolLastUpdate")
                .appsolLastEdit = tempRow.Item("appsolLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    ' for reading records
    '
    Public Sub moveDataRowToProperties _
            (ByVal tempRow As DataRow, _
             ByRef tempDC As dc_AppSol)

        Try

            With tempDC
                .initProperties()
                .appsolKey = tempRow.Item("appsolKey")
                .appsolClientKey = tempRow.Item("appsolClientKey")
                '*******************************************************
                .appsolName = tempRow.Item("appsolName")

                .appsolComments = tempRow.Item("appsolComments")
                .appsolLastUpdate = tempRow.Item("appsolLastUpdate")
                .appsolLastEdit = tempRow.Item("appsolLastEdit")
            End With


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' for moving active table to collection for wpf
    '
    Public Sub moveDataTableToObservableCollection _
                (ByVal dtTemp As DataTable)

        Try

            If 0 < dtTemp.Rows.Count Then

                If m_oc IsNot Nothing Then
                    m_oc = Nothing
                    m_oc = New Collection(Of dc_AppSol)
                Else
                    m_oc = New Collection(Of dc_AppSol)
                End If

                Dim drTemp As DataRow
                Dim dcTemp As dc_AppSol
                For Each drTemp In dtTemp.Rows
                    dcTemp = New dc_AppSol
                    moveDataRowToProperties(drTemp, dcTemp)
                    m_oc.Add(dcTemp)
                Next

            End If

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub


    '
    '
    '
    Public Function moveDataTableToXML() As XmlDocument

        Dim writer = New System.IO.StringWriter
        Dim m_dt_xml As Xml.XmlDocument = Nothing

        Try


            m_dt.WriteXml(writer, XmlWriteMode.WriteSchema, True)
            m_dt_xml = Nothing
            m_dt_xml = New Xml.XmlDocument
            m_dt_xml.LoadXml(writer.ToString)

        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally

            writer = Nothing

        End Try

        Return m_dt_xml


    End Function

#End Region

#Region " standard calls  "

    '
    '
    '
    Public Sub initDataSetAndTable()

        Try

            ' make sure the root stuff is there
            If m_ds Is Nothing Then
                m_ds = New DataSet
            Else
                m_ds = Nothing
                m_ds = New DataSet
            End If
            m_ds.SchemaSerializationMode = SchemaSerializationMode.IncludeSchema

            If m_dt Is Nothing Then
                m_dt = New DataTable
            Else
                m_dt = Nothing
                m_dt = New DataTable
            End If

            If m_oc Is Nothing Then
                m_oc = New Collection(Of dc_AppSol)
            Else
                m_oc = Nothing
                m_oc = New Collection(Of dc_AppSol)
            End If

            If m_dc Is Nothing Then
                m_dc = New dc_AppSol(strUserId)
            Else
                m_dc = Nothing
                m_dc = New dc_AppSol(strUserId)
            End If


            CreateTableColumns(m_dt)
            m_dt.TableName = DC_RackTables.tblAppSolution.ToString
            m_ds.Tables.Add(m_dt)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try



    End Sub


    '
    '
    '
    Private Sub buildUpdateAppSolutionCommand()

        Try
            m_updateCommand = m_db.GetStoredProcCommand("updateAppSolution")

            Dim cmdActive As DbCommand = m_updateCommand

            m_db.AddInParameter(cmdActive, "appsolKey", DbType.Int32, "appsolKey", DataRowVersion.Current)
            '**************************************************
            m_db.AddInParameter(cmdActive, "appsolClientKey", DbType.Int32, "appsolClientKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolName", DbType.String, "appsolName", DataRowVersion.Current)

            m_db.AddInParameter(cmdActive, "appsolComments", DbType.String, "appsolComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolLastUpdate", DbType.Date, "appsolLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolLastEdit", DbType.String, "appsolLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildInsertAppSolutionCommand()

        Try
            m_insertCommand = m_db.GetStoredProcCommand("insertAppSolution")

            Dim cmdActive As DbCommand = m_insertCommand

            '**********************************************************
            m_db.AddInParameter(cmdActive, "appsolClientKey", DbType.Int32, "appsolClientKey", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolName", DbType.String, "appsolName", DataRowVersion.Current)

            m_db.AddInParameter(cmdActive, "appsolComments", DbType.String, "appsolComments", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolLastUpdate", DbType.Date, "appsolLastUpdate", DataRowVersion.Current)
            m_db.AddInParameter(cmdActive, "appsolLastEdit", DbType.String, "appsolLastEdit", DataRowVersion.Current)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    '
    '
    Private Sub buildDeleteAppSolutionCommand()

        Try
            m_deleteCommand = m_db.GetStoredProcCommand("deleteAppSolution")

            Dim cmdActive As DbCommand = m_deleteCommand
            m_db.AddInParameter(cmdActive, "appsolKey", DbType.Int32, "appsolKey", DataRowVersion.Current)

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub

    '
    ' create a record based on property values move it to the generic datatable
    ' then copy the table to the database
    '
    Public Function addAppSolutionDataSourceRecord _
            (ByVal tempDC As dc_AppSol, _
             Optional ByVal blnSilent As Boolean = False) _
             As Integer

        Dim intRowsAffected As Integer = 0

        Try

            initDataSetAndTable()

            Dim m_dr As DataRow = m_ds.Tables(DC_RackTables.tblAppSolution.ToString).NewRow
            movePropertiesToDataRow(m_dr, tempDC)
            m_ds.Tables(DC_RackTables.tblAppSolution.ToString).Rows.Add(m_dr)
            intRowsAffected = dbUpdateDataSet()

            If blnSilent = False Then
                MsgBox("DataRowsUpated = " & intRowsAffected)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

        Return intRowsAffected

    End Function

    '
    '
    '
    Public Function dbUpdateDataSet() As Int16

        Dim rowsAffected As Int16 = 0

        Try

            rowsAffected = m_db.UpdateDataSet(m_ds, DC_RackTables.tblAppSolution.ToString, _
                                        m_insertCommand, _
                                        m_updateCommand, _
                                        m_deleteCommand, _
                                        UpdateBehavior.Standard)
            'msgbox rowsAffected

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If

        End Try

        Return rowsAffected

    End Function




#End Region

#Region " Database Calls "

    '
    '
    '
    Public Sub dbSelectAllAppSolutionData()


        Try


            initDataSetAndTable()

            ' Retrieve the initial data
            Dim cmd As DbCommand = m_db.GetStoredProcCommand(readAll)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAppSolution.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAppSolution.ToString)


        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try

    End Sub


    '
    '
    '
    Public Sub dbSelectAppSolutionByClientKey(ByVal intClientKey As Int32)


        Try

            Dim cmd As DbCommand = m_db.GetStoredProcCommand("selectAppSolutionByClientKey")
            m_db.AddInParameter(cmd, "@appsolClientKey", DbType.Int32, intClientKey)
            m_db.LoadDataSet(cmd, m_ds, DC_RackTables.tblAppSolution.ToString)

            m_dt = m_ds.Tables(DC_RackTables.tblAppSolution.ToString)

            If m_dt.Rows.Count = 1 Then
                Dim m_dr As System.Data.DataRow = m_dt.Rows(0)
                moveDataRowToProperties(m_dr)
            End If

        Catch sql As SqlException
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(sql, "Database Policy")
            If (rethrow) Then
                Throw
            End If
        Catch Ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(Ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        Finally
        End Try

    End Sub


#End Region

#Region " Constructor "

    Public Sub New()
        Try


            m_db = DatabaseFactory.CreateDatabase()

            strUserId = Environment.UserDomainName & "/" & Environment.UserName

            '
            ' init an object class so that the default constants are available
            ' to the table definitions
            '


            initDataSetAndTable()

            m_updateCommand = Nothing
            m_insertCommand = Nothing
            m_deleteCommand = Nothing

            buildInsertAppSolutionCommand()
            buildDeleteAppSolutionCommand()
            buildUpdateAppSolutionCommand()



        Catch ex As Exception
            Dim rethrow As Boolean = ExceptionPolicy.HandleException(ex, "Log Only Policy")
            If (rethrow) Then
                Throw
            End If
        End Try




    End Sub


#End Region




End Class
